package com.ztp.lib.client;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.ztp.lib.client.Client.ValidationException;

public class SQLiteFtpClientsManager implements FtpClientsManager{
	private static final String URL = "jdbc:sqlite:ftpclients.db";
	private static SQLiteFtpClientsManager instance;
	private Connection conn = null;
	
	SQLiteFtpClientsManager() throws SQLException, ClassNotFoundException{
		Class.forName("org.sqlite.JDBC");
		conn = DriverManager.getConnection(URL);	
		
		Statement stm = conn.createStatement();
		//stm.executeUpdate("drop table if exists ftpclients");
		stm.executeUpdate("create table if not exists ftpclients(" +
				"id integer PRIMARY KEY AUTOINCREMENT not null," +
				"hostname varchar(256) not null," +
				"username varchar(256) not null," +
				"password varchar(256) not null," +
				"port integer not null," +
				"ssl boolean not null)");
		conn.close();
		conn = null;
	}

	
	public static SQLiteFtpClientsManager getInstance() throws ClassNotFoundException, SQLException{
		if(instance == null){
			instance = new SQLiteFtpClientsManager();
		}
		return instance;
	}
	
	@Override
	public Client[] getAllClients() throws IOException {
		Client[] clients = null;
		ResultSet rs = null;
		try {
			conn = DriverManager.getConnection(URL);
			Statement stm = conn.createStatement();
			rs = stm.executeQuery("select count(*) from ftpclients");
			rs.next();
			int count = rs.getInt(1);
			clients = new Client[count];
			rs.close();
			rs = null;
			
			rs =  stm.executeQuery("select * from ftpclients");
			
			count = 0;
			
			try{
				while(rs.next()){
					clients[count++] = new Client(rs.getInt(1)
							, rs.getString(2)
							, rs.getString(3)
							, rs.getString(4)
							, rs.getInt(5)
							, rs.getBoolean(6));
				}
			}
			catch(ValidationException ve){
				throw new IOException(ve);
			}
			
		} catch (SQLException e) {
			throw new IOException(e);
		}
		finally{
			if(rs != null){
				try {
					rs.close();
				} catch (SQLException e) {}
			}
			if(conn != null){
				try {
					conn.close();
				} catch (SQLException e) {}
				conn = null;
			}
			
		}
		return clients;
	}

	@Override
	public void addNew(Client c) throws IOException {
		try {
			conn = DriverManager.getConnection(URL);
			Statement stm = conn.createStatement();
			stm.executeUpdate("insert into ftpclients (hostname,username,password,port,ssl) values ('"
			+c.hostName+"','"+c.userName+"','"+c.password+"',"+c.port+","+((c.ssl)?"1":"0")+")");
		} catch (SQLException e) {
			throw new IOException(e);
		}
		finally{
			if(conn != null){
				try {
					conn.close();
				} catch (SQLException e) {}
				conn = null;
			}
		}
	}

	@Override
	public void save(Client c) throws IOException {
		try {
			conn = DriverManager.getConnection(URL);
			Statement stm = conn.createStatement();
			stm.executeUpdate("update ftpclients SET hostname='"+c.hostName+"', username='"+c.userName+"', password='"+c.password+"'" +
					", port="+c.port+", ssl="+((c.ssl)?"1":"0")+" where id=" + c.id);
		} catch (SQLException e) {
			throw new IOException(e);
		}
		finally{
			if(conn != null){
				try {
					conn.close();
				} catch (SQLException e) {}
				conn = null;
			}
		}
	}

	@Override
	public void delete(int clientId) throws IOException {
		try {
			conn = DriverManager.getConnection(URL);
			Statement stm = conn.createStatement();
			stm.executeUpdate("delete from ftpclients where id=" + clientId);
		} catch (SQLException e) {
			throw new IOException(e);
		}
		finally{
			if(conn != null){
				try {
					conn.close();
				} catch (SQLException e) {}
				conn = null;
			}
		}
	}
}
